package com.dao.impl;

import com.dao.IStudentsDAO;
import com.db.ConnectionManager;
import com.vo.Students;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class StudentsDAOImpl implements IStudentsDAO {
    @Override
    public boolean insertStudents(Students students) throws Exception {
        Connection con = null;
        PreparedStatement pstmt = null;
        boolean flag = false;
        try{
            con = ConnectionManager.getConnection();  //1.创建连接
            String strSql = "insert into students values(?,?,?,?,?)";
            pstmt = con.prepareStatement(strSql);  //2.创建预处理语句总管
            pstmt.setString(1,students.getStudentId());
            pstmt.setString(2,students.getStudentName());
            pstmt.setString(3,students.getStudentPwd());
            pstmt.setString(4,students.getGender());
            pstmt.setInt(5,students.getClassId());
            if(pstmt.executeUpdate() > 0){ //3.语句总管执行SQL语句
                flag = true;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            ConnectionManager.closeStatement(pstmt);
            ConnectionManager.closeConnection(con);
        }
        return flag;
    }

    @Override
    public boolean deleteStudents(String studentId) throws Exception {
        Connection con = null;
        PreparedStatement pstmt = null;
        boolean flag = false;
        try{
            con = ConnectionManager.getConnection();
            String strSql = "delete from students where studentId = ?";
            pstmt = con.prepareStatement(strSql);
            pstmt.setString(1,studentId);
            if(pstmt.executeUpdate() > 0){
                flag = true;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            ConnectionManager.closeStatement(pstmt);
            ConnectionManager.closeConnection(con);
        }

        return flag;
    }

    @Override
    public boolean updateStudents(Students students) throws Exception {
        Connection con = null;
        PreparedStatement pstmt = null;
        boolean isSuccess = false;
        try{
            con = ConnectionManager.getConnection();
            String sql = "update students set studentName = ?, studentPwd = ?, gender = ?, classId = ? where studentId = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1,students.getStudentName());
            pstmt.setString(2,students.getStudentPwd());
            pstmt.setString(3,students.getGender());
            pstmt.setInt(4,students.getClassId());
            pstmt.setString(5,students.getStudentId());
            if(pstmt.executeUpdate() > 0){
                isSuccess = true;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            ConnectionManager.closeStatement(pstmt);
            ConnectionManager.closeConnection(con);
        }

        return isSuccess;
    }

    @Override
    public List<Students> findAllStudents() throws Exception {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Students> all = new ArrayList<Students>();
        try{
            con = ConnectionManager.getConnection();  //1.创建连接
            String strSql = "select * from students";
            pstmt = con.prepareStatement(strSql);  //2.创建预处理语句总管
            rs = pstmt.executeQuery(); //3.语句总管执行SQL语句
            Students stu = null;
            while(rs.next()){ //4.处理结果集
                stu = new Students();
                stu.setStudentId(rs.getString("studentId"));
                stu.setStudentName(rs.getString("studentName"));
                stu.setStudentPwd(rs.getString(3));
                stu.setGender(rs.getString(4));
                stu.setClassId(rs.getInt("classId"));
                all.add(stu);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            ConnectionManager.closeStatement(pstmt);
            ConnectionManager.closeConnection(con);
        }
        return all;
    }

    @Override
    public Students findStudentsById(String studentId) throws Exception {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Students stu = null;

        try{
            con = ConnectionManager.getConnection();    //1.创建连接
            String strSql = "select * from students where studentId = ?";
            pstmt = con.prepareStatement(strSql);
            pstmt.setString(1, studentId);
            rs = pstmt.executeQuery();
            if(rs.next()){
                stu = new Students();
                stu.setStudentId(rs.getString("studentId"));
                stu.setStudentName(rs.getString("studentName"));
                stu.setStudentPwd(rs.getString(3));
                stu.setGender(rs.getString(4));
                stu.setClassId(rs.getInt("classId"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            ConnectionManager.closeStatement(pstmt);
            ConnectionManager.closeConnection(con);
        }

        return stu;
    }

    @Override
    public List<Students> searchStudents(String keyword) throws Exception {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Students> result = new ArrayList<Students>();

        try {
            con = ConnectionManager.getConnection();
            String sql = "select * from students where studentId like ? or studentName like ? or gender like ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, "%" + keyword + "%");
            pstmt.setString(2, "%" + keyword + "%");
            pstmt.setString(3, "%" + keyword + "%");
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Students stu = new Students();
                stu.setStudentId(rs.getString("studentId"));
                stu.setStudentName(rs.getString("studentName"));
                stu.setStudentPwd(rs.getString("studentPwd"));
                stu.setGender(rs.getString("gender"));
                stu.setClassId(rs.getInt("classId"));
                result.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeResultSet(rs);
            ConnectionManager.closeStatement(pstmt);
            ConnectionManager.closeConnection(con);
        }

        return result;
    }
}
